BEGIN;

/* users */

CREATE TABLE users (
 	id INTEGER PRIMARY KEY,
	uuid TEXT UNIQUE,
	path TEXT UNIQUE NOT NULL,
	username TEXT NOT NULL,
	password TEXT NOT NULL,
	firstName TEXT NOT NULL,
	lastName TEXT NOT NULL,
	email TEXT NOT NULL,
	modified DATE NOT NULL,
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);


/* templates */

CREATE TABLE templates (
 	id INTEGER PRIMARY KEY,
	uuid TEXT UNIQUE,
	user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
	path TEXT UNIQUE NOT NULL,
	title TEXT NOT NULL DEFAULT '',
	author TEXT NOT NULL DEFAULT '',
	thumbnail OBJECT,
	html TEXT NOT NULL DEFAULT '',
	css TEXT NOT NULL DEFAULT '',
	modified DATE NOT NULL,
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);


/* fields */

CREATE TABLE fields (
	id INTEGER PRIMARY KEY,
	uuid TEXT UNIQUE,
	template_id INTEGER NOT NULL REFERENCES templates (id) ON DELETE CASCADE ON UPDATE CASCADE,
	name TEXT NOT NULL,
	type TEXT DEFAULT 'html', /* html, text, links, or page */
	searchable BOOLEAN NOT NULL DEFAULT false,
	shareable BOOLEAN NOT NULL DEFAULT false,
	modified DATE NOT NULL,
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX fields_template_id ON fields (template_id);


/* pages */

CREATE TABLE pages (
 	id INTEGER PRIMARY KEY,
	uuid TEXT UNIQUE,
	user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
	path TEXT UNIQUE NOT NULL,
	tags TEXT NOT NULL DEFAULT '',
	modified DATE NOT NULL,
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX pages_user_id ON pages (user_id);


/* revisions */

CREATE TABLE revisions (
 	id INTEGER PRIMARY KEY,
	uuid TEXT UNIQUE,
	user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
	template_id INTEGER NOT NULL REFERENCES templates (id) ON DELETE CASCADE ON UPDATE CASCADE,
	page_id INTEGER NOT NULL REFERENCES pages (id) ON DELETE CASCADE ON UPDATE CASCADE,
	publish DATE,
	expire DATE,
	modified DATE NOT NULL,
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX revisions_user_id ON revisions (user_id);
CREATE INDEX revisions_template_id ON revisions (template_id);
CREATE INDEX revisions_page_id ON revisions (page_id);
CREATE INDEX revisions_publish ON revisions (publish);


/* contents */

CREATE TABLE contents (
	id INTEGER PRIMARY KEY,
	uuid TEXT UNIQUE,
	user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
	revision_id INTEGER NOT NULL REFERENCES revisions (id) ON DELETE CASCADE ON UPDATE CASCADE,
	page_id INTEGER REFERENCES pages (id) ON DELETE SET NULL ON UPDATE CASCADE, /* for content that points to another page */
	field TEXT NOT NULL,
	value TEXT NOT NULL DEFAULT '',
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX contents_user_id ON contents (user_id);
CREATE INDEX contents_revision_id ON contents (revision_id);


/* comments */

CREATE TABLE comments (
	id INTEGER PRIMARY KEY,
	uuid TEXT UNIQUE,
	user_id INTEGER REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
	page_id INTEGER NOT NULL REFERENCES pages (id) ON DELETE CASCADE ON UPDATE CASCADE,
	approved BOOLEAN NOT NULL DEFAULT false,
	name TEXT NOT NULL,
	email TEXT NOT NULL,
	website TEXT NOT NULL,
	value TEXT NOT NULL DEFAULT '',
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX comments_user_id ON comments (user_id);
CREATE INDEX comments_page_id ON comments (page_id);


/* files */

CREATE TABLE files (
 	id INTEGER PRIMARY KEY,
	uuid TEXT UNIQUE,
	user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
	path TEXT UNIQUE NOT NULL,
	mime TEXT NOT NULL,
	content OBJECT,
	modified DATE NOT NULL,
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX files_user_id ON files (user_id);


/* users to pages permission */

CREATE TABLE users_pages (
	user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
	page_id INTEGER NOT NULL REFERENCES pages (id) ON DELETE CASCADE ON UPDATE CASCADE,
	`create` BOOLEAN NOT NULL,
	read BOOLEAN NOT NULL,
	`update` BOOLEAN NOT NULL,
	`delete` BOOLEAN NOT NULL,
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY	(user_id, page_id)
);


/* users to templates permission */

CREATE TABLE users_templates (
	user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
	template_id INTEGER NOT NULL REFERENCES pages (id) ON DELETE CASCADE ON UPDATE CASCADE,
	`create` BOOLEAN NOT NULL,
	read BOOLEAN NOT NULL,
	`update` BOOLEAN NOT NULL,
	`delete` BOOLEAN NOT NULL,
	created DATE NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY	(user_id, template_id)
);

COMMIT;
